﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using Follow.Utils;
using Follow.Model;
using System.Transactions;

namespace Follow.DataAccess
{
    public class PropostaDAO
    {
        private SqlDataReader reader = null;
        private SqlConnection conn = null;
        private SqlCommand cmd = null;

        public PropostaDAO()
        {
            conn = ConnectionUtils.getConnection();
        }

        public IList<Proposta> findByFunc(Int32 id)
        {
            IList<Proposta> propostas = new List<Proposta>();
            try
            {
                conn.Open();
                String sqlCommand = " select * from proposta where funcionario_idfuncionario = @id; ";
                cmd = new SqlCommand(sqlCommand, conn);
                cmd.Parameters.Add(new SqlParameter("id", id));
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Proposta pro = new Proposta();
                    pro.IDProposta = reader.GetInt32(0);
                    pro.IDFuncionario = reader.GetInt32(1);
                    pro.IDCliente = reader.GetInt32(3);
                    pro.DataProposta = reader.GetDateTime(4);
                    pro.ObsProposta = reader.GetString(5);
                    pro.ValorTotal = reader.GetDecimal(6);
                    pro.PrazoEntrega = reader.GetString(7);
                    pro.Desconto = reader.GetFloat(8);
                    propostas.Add(pro);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return propostas;
        }

        public IList<Proposta> findAll()
        {
            IList<Proposta> proposta = new List<Proposta>();

            try
            {
                conn.Open();
                String sqlCommand = " select * from proposta order by dataproposta;";
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Proposta pro = new Proposta();
                    pro.IDProposta = reader.GetInt32(0);
                    pro.IDFuncionario = reader.GetInt32(1);
                    pro.IDCliente = reader.GetInt32(3);
                    pro.DataProposta = reader.GetDateTime(4);
                    pro.ObsProposta = reader.GetString(5);
                    pro.ValorTotal = reader.GetDecimal(6);
                    pro.PrazoEntrega = reader.GetString(7);
                    pro.Desconto = reader.GetFloat(8);
                    proposta.Add(pro);
                }
            }

            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }

            return proposta;
        }

        public int insert(Proposta p, Int32 idlogin)
        {
            int idProposta = 0;
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    String sqlCommand = " insert into proposta (funcionario_idfuncionario, cliente_idcliente," +
                                        " dataproposta, observacoes, valortotal, prazoentrega, desconto) values (" + p.IDFuncionario + "," +
                                        p.IDCliente + " , getdate(), '" + p.ObsProposta + "', 0, '" + p.PrazoEntrega + "', 0);";
                    sqlCommand += "select * from proposta where idProposta= @@IDENTITY;";

                    cmd = new SqlCommand(sqlCommand, conn);

                    reader = cmd.ExecuteReader();
                    transaction.Complete();
                    while (reader.Read())
                    {
                        idProposta = Convert.ToInt32(reader[0]);
                    }
                }

                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }

                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
                LogDAO log = new LogDAO();
                log.insert(idlogin, Common.SQLStringSingleton.GetCommandText(cmd), "Insert", "Proposta");
            }

            return idProposta;
        }

        public void updateProposta(Proposta p, Int32 idlogin)
        {
            int idProposta = 0;
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    String sqlCommand = " update proposta set funcionario_idfuncionario = " + p.IDFuncionario + ", cliente_idcliente = " + p.IDCliente +
                                        " ,dataproposta = '" + p.DataProposta + "', observacoes = '" + p.ObsProposta + "' ,valortotal = " + p.ValorTotal + ", prazoentrega = '" + p.PrazoEntrega +
                                        "' , desconto = " + p.Desconto + " where idProposta = " + p.IDProposta;

                    cmd = new SqlCommand(sqlCommand, conn);

                    cmd.ExecuteNonQuery();
                    transaction.Complete();
                }

                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }

                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
                LogDAO log = new LogDAO();
                log.insert(idlogin, Common.SQLStringSingleton.GetCommandText(cmd), "Update", "Proposta");
            }

        }

        public void update(int idProposta, double valorTotal, double desconto, Int32 idlogin)
        {
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    String sqlCommand = " update proposta set" +
                                        " valortotal = @valortotal,  desconto = @desconto where idproposta = @idproposta;";
                    cmd = new SqlCommand(sqlCommand, conn);
                    cmd.Parameters.Add(new SqlParameter("idproposta", idProposta));
                    cmd.Parameters.Add(new SqlParameter("valortotal", valorTotal));
                    cmd.Parameters.Add(new SqlParameter("desconto", desconto));
                    cmd.ExecuteNonQuery();
                    transaction.Complete();
                }
                catch (Exception)
                {
                    transaction.Dispose();
                    throw;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }

                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
                LogDAO log = new LogDAO();
                log.insert(idlogin, Common.SQLStringSingleton.GetCommandText(cmd), "Update", "Proposta");
            }
        }

        public void delete(Int32 id, Int32 idlogin)
        {
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    String sqlCommand = " delete from itemproposta where proposta_idproposta = @id;";
                    sqlCommand += " delete from followup where proposta_idproposta = @id;";
                    sqlCommand += "delete from proposta where idproposta = @id;" ;
                    cmd = new SqlCommand(sqlCommand, conn);
                    cmd.Parameters.Add(new SqlParameter("id", id));
                    cmd.ExecuteNonQuery();
                    transaction.Complete();
                }
                catch (Exception)
                {
                    transaction.Dispose();
                    throw;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }

                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
                LogDAO log = new LogDAO();
                log.insert(idlogin, Common.SQLStringSingleton.GetCommandText(cmd), "Delete", "Proposta");
            }
        }

        public Proposta findByID(Int32 id)
        {
            Proposta proposta = null;
            try
            {
                conn.Open();
                String sqlCommand = " select * from proposta where idproposta = @id;";
                cmd = new SqlCommand(sqlCommand, conn);
                cmd.Parameters.Add(new SqlParameter("id", id));
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    proposta = new Proposta();
                    proposta.IDProposta = reader.GetInt32(0);
                    proposta.IDFuncionario = reader.GetInt32(1);
                    proposta.DataProposta = reader.GetDateTime(3);
                    proposta.ObsProposta = reader.GetString(4);
                    proposta.ValorTotal = reader.GetDecimal(5);
                    proposta.PrazoEntrega = reader.GetString(6);
                    proposta.Desconto = reader.GetFloat(7);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return proposta;
        }

        public Proposta findByDataProposta(DateTime dataproposta)
        {
            Proposta proposta = null;
            try
            {
                conn.Open();
                String sqlCommand = " select * from proposta where dataproposta = @data order by dataproposta";
                cmd = new SqlCommand(sqlCommand, conn);
                cmd.Parameters.Add(new SqlParameter("data", dataproposta));
                reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    proposta = new Proposta();
                    proposta.IDProposta = reader.GetInt32(0);
                    proposta.IDFuncionario = reader.GetInt32(1);
                    proposta.DataProposta = reader.GetDateTime(3);
                    proposta.ObsProposta = reader.GetString(4);
                    proposta.ValorTotal = reader.GetDecimal(5);
                    proposta.PrazoEntrega = reader.GetString(6);
                    proposta.Desconto = reader.GetFloat(7);
                }

            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return proposta;
        }

        public Cliente findByIdCliente(Int32 id)
        {
            Cliente c = null;
            try
            {
                conn.Open();
                String sqlCommand = " select * from proposta where cliente_idcliente = @id;";
                cmd = new SqlCommand(sqlCommand, conn);
                cmd.Parameters.Add(new SqlParameter("id", id));
                reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    c = new Cliente();
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return c;
        }

    }

}